from DataAccess.DBConnFactory import *

from Misc.Utils import *
from string import Template

def query_cash_dividend(ref_date, tick_list = None):

	sql_tpl = Template('''select divd.SYMBOL,
							SUM(Dishty3)/10 as cash_dvd_rate
						from DisHty divd
							${TICK_CRITERIA}
							and DisHty13=TO_DATE('${DATE}', 'yyyy-mm-dd')
						GROUP BY divd.SYMBOL having SUM(Dishty3)>0''')
							
	sql_text = sql_tpl.substitute(DATE = ref_date.isoformat(),
									TICK_CRITERIA = format_sql_tick_criteria('divd.SYMBOL', tick_list))
	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	cash_divd = {}
	for elem in r:
		cash_divd[elem[0]] = elem[1]
		
	return cash_divd
		
def query_share_dividend(ref_date, tick_list = None):

	sql_tpl = Template('''select divd.SYMBOL,
							SUM(Dishty7+Dishty8)/10 as shares_dvd_rate 
						from DisHty divd
							${TICK_CRITERIA}
							and DisHty13=TO_DATE('${DATE}', 'yyyy-mm-dd')
						GROUP BY divd.SYMBOL having SUM(Dishty7 + Dishty8)>0''')
							
	sql_text = sql_tpl.substitute(DATE = ref_date.isoformat(),
									TICK_CRITERIA = format_sql_tick_criteria('divd.SYMBOL', tick_list))
	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	shares_divd = {}
	for elem in r:
		shares_divd[elem[0]] = elem[1]
		
	return shares_divd
	
	